﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using NPOI;
using System.Data.OleDb;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;

namespace ly.Web.Admin
{
    public partial class workManager : System.Web.UI.Page
    {
        ly.Model.Activity actModel = new Model.Activity();
        ly.BLL.Activity actBll = new BLL.Activity();
        ly.Model.UserInfo userModel = new Model.UserInfo();
        ly.BLL.UserInfo userBll = new BLL.UserInfo();
        protected void Page_Load(object sender, EventArgs e)
        {
            



        }

        protected void btn_upload_Click(object sender, EventArgs e)
        {
            string path = "";
            if (FileUpload.HasFile)
            {
                string fileExrensio = System.IO.Path.GetExtension(FileUpload.FileName).ToLower();//ToLower转化为小写
                string UploadURL = Server.MapPath("~/files/");//上传的目录
                if (fileExrensio == ".xlsx"|| fileExrensio == ".xls")//判断文件类型
                {
                    try
                    {
                        if (!System.IO.Directory.Exists(UploadURL))//判断文件夹是否已经存在
                        {
                            System.IO.Directory.CreateDirectory(UploadURL);//创建文件夹
                        }
                        string filename = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + Path.GetFileName(FileUpload.FileName).ToString();
                        path = UploadURL + filename + fileExrensio;
                        //string filename = Path.GetFileName(FileUpload.FileName);
                        this.FileUpload.PostedFile.SaveAs(path);  //保存上传的文件
                        //上传成功
                        lblmsg.Text = "上传成功";
                    }
                    catch
                    {
                        lblmsg.Text = "上传发生错误，传输失败！";
                        return;
                    }
                }
                else
                {
                    lblmsg.Text = "本系统只支持excel(*.xls)文件格式导入，可以参考摸板！";
                    return;
                }
            }
            else
            {
                lblmsg.Text = "请选择导入文件！";
                return;
            }
            DataSet ds = ExcelToDataSet(path);
            //导入成功后，删除服务器上的文件
            if (File.Exists(path))
            {
                File.Delete(path);
            }
            foreach (DataTable table in ds.Tables)
            {
                foreach (DataRow row in table.Rows)
                {
                    string loginname = row[1].ToString().Trim();
                    //根据用户名查找数据库，判断是否已经存在
                    int yhid = userBll.GetList(" where  UserName='" + loginname + "'").Tables[0].Rows.Count;
                    if (yhid > 0)//已存在
                    {
                        lblmsg.Text = "导入失败，已经存在用户!";
                        return;
                    }
                }
                foreach (DataRow row in table.Rows)
                {
                    userModel.UserName = row[0].ToString().Trim();
                    userModel.Pasword = row[1].ToString().Trim();
                    //userModel.QQ = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(row[2].ToString().Trim(), "MD5");
                    //us.utype = 3;
                    //us.department = department;
                    //us.checker = 0;
                    //us.createrid = uid;
                    //int userIdentity = usersbll.Add(us);
                }
            }
            lblmsg.Text = "导入成功！";
            Page.ClientScript.RegisterStartupScript(GetType(), "", "<script>alert('导入成功！')</script>");
       }

        /// <summary>
        /// 读取当前Execl文件返回DataSet对象
        /// </summary>
        /// <param name="filepath"></param>
        /// <returns></returns>
        private static DataSet ExcelToDataSet(string filepath)
        {
            DataSet ds;
            string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;data source=" + filepath +
                            ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            OleDbConnection myConn = new OleDbConnection(strCon);
            string strCom = " SELECT * FROM [用户信息$]";
            myConn.Open();
            OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
            ds = new DataSet();
            myCommand.Fill(ds);
            myConn.Close();
            return ds;
        }
        /// <summary>
        /// 将文件保存到指定目录下
        /// </summary>
        /// <param name="ms">数据转换后的流</param>
        /// <param name="fileName">保存文件路径</param>
        private void SaveToFile(MemoryStream ms, string fileName)
        {
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
                data = null;
            }

        }



        /// <summary>
        /// 将DataTable转换成流(必须导入NPOI.dll包)
        /// </summary>
        /// <param name="dataSource">当前要写入的数据源</param>
        /// <param name="workname">工作表名</param>
        /// <returns></returns>
        public static MemoryStream GetTable(DataTable dataSource, string workname)
        {
            MemoryStream ms = new MemoryStream();
            IWorkbook workbook = new HSSFWorkbook();//创建Workbook对象
            ISheet sheet = workbook.CreateSheet(workname);
            IRow headerRow = sheet.CreateRow(0);

            foreach (DataColumn column in dataSource.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);

            int rowIndex = 1;
            foreach (DataRow row in dataSource.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dataSource.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }
                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return ms;
        }




        protected void btntianjia_Click(object sender, EventArgs e)
        {
            actModel.ActivityName = txtworkname.Text.Trim();
            actModel.UserName = txtusername.Text.Trim();
            actModel.EndTime = Convert.ToDateTime(startTime.Value.Trim());
            actModel.ActivityIntroduction = FCKeditor1.Value.Trim();
            int cou = actBll.Add(actModel);
            if (cou > 0)
            {
                ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('添加成功');</script>");
            }
            else
            {
                ClientScript.RegisterStartupScript(this.GetType(), "", "<script>alert('添加失败');</script>");
            }

        }

        protected void lbuser_Click(object sender, EventArgs e)
        {
            //DataTable dt = actBll.GetList1(" 1=0").Tables[0];
            //string path = Server.MapPath("~/files/users/") + "用户信息模板.xls";
            //if (File.Exists(path))
            //    File.Delete(path);
            //SaveToFile(GetTable(dt, "用户信息"), Server.MapPath("~/files/users/") + "用户信息模板.xls");
            //Response.Redirect("~/files/users/用户信息模板.xls");
        }  
    }
}